package com.larry.motion.service.impl;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;

import com.larry.motion.vo.RegionType;
import com.pegatron.motion.stub.AddOrUpdate;
import com.pegatron.motion.stub.Case;
import com.pegatron.motion.stub.CustomFieldValue;
import com.pegatron.motion.stub.CustomFieldValues;
import com.pegatron.motion.stub.Request;
import com.pegatron.motion.stub.Smbxml;

public class MotionCSService extends MotionService {

	public MotionCSService(String region) {
		super(region);
		this.region = region;
	}

	@Override
	public String genFileName(Integer index) throws Exception {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		String dateStr = sdf.format(new Date());
		String key = RegionType.valueOf(region).getFileKey();
		new File(folder + dateStr).mkdirs();
		return folder + dateStr + "/" + "R02" + key + "CMP" + dateStr + "." + index + ".xml";
	}

	@Override
	public Boolean genMessageFile(String fileName, String orderNr) throws Exception {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			JAXBContext jc = JAXBContext.newInstance(STUB_CONTEXT_PATH);
			Marshaller m = jc.createMarshaller();
			m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
			m.setProperty(Marshaller.JAXB_FRAGMENT, Boolean.TRUE);
			conn = super.getConnection();
			StringBuffer buf = this.getSQL(orderNr);
			statement = conn.prepareStatement(buf.toString());
			rs = statement.executeQuery();
			File file = new File(fileName);
			FileWriter fw;
			Smbxml smb = new Smbxml();
			smb.setMissingFieldOverwrite("false");
			smb = this.processMessage(smb, "CS", rs);
			fw = new FileWriter(file, true);
			fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
			fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_4_13.dtd\">\n");
			m.marshal(smb, fw);
			fw.close();
			return true;
		} catch (IOException e) {
			e.printStackTrace();
		} catch (JAXBException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			super.closeConnection(conn, statement, rs);
		}
		return false;
	}

	@Override
	public StringBuffer getSQL(String orderNr) throws Exception {
		StringBuffer buf = new StringBuffer();
		buf.append("	select");
		buf.append("		x.order_no,");
		buf.append("		x.rma_site,");
		buf.append("		x.case_number,");
		buf.append("		x.case_handle,");
		buf.append("		x.case_entity_handle,");
		buf.append("		x.receive_date,");
		buf.append("		x.ship_date,");
		buf.append("		x.closed_date,");
		buf.append("		x.track_nr,");
		buf.append("		x.track_nr_rtn,");
		buf.append("		x.oem_pn part_nr,");
		buf.append("		x.order_keyin_no,");
		buf.append("		count(*) qty");
		buf.append("	from (");
		buf.append("		select");
		buf.append("			a.order_no,");
		buf.append("			a.rma_site,");
		buf.append("			e.item_no,");
		buf.append("			b.customer_po case_number,");
		buf.append("			b.customer_pn case_handle,");
		buf.append("			c.mrb case_entity_handle,");
		buf.append("			d.custom_serial_no serial_nr,");
		buf.append("			to_char(ers_util_p.getorderreceivedate(a.order_no,a.rma_site),'MM/DD/YYYY') receive_date,");
		buf.append("			to_char(nvl((select max(x.ct_date) from ers_order_track x where x.status='SHIP' and x.order_no=a.order_no and x.rma_site=a.rma_site),(select max(x.ct_date) from ers_order_track x where x.order_no=a.order_no and x.rma_site=a.rma_site)),'MM/DD/YYYY') ship_date,");
		buf.append("			to_char(nvl((select max(x.ct_date) from ers_order_track x where x.status='CLOSED' and x.order_no=a.order_no and x.rma_site=a.rma_site),(select max(x.ct_date) from ers_order_track x where x.order_no=a.order_no and x.rma_site=a.rma_site)),'MM/DD/YYYY') closed_date,");
		buf.append("			(select x.track_nr from ers_forwarder_track x where x.dt_id is null and x.status='CSOSHIP' and x.direct_type='OUT' and x.order_nr=a.order_nr and x.rma_site=a.rma_site and rownum=1) track_nr,");
		buf.append("			(select x.track_nr from ers_forwarder_track x where x.dt_id is null and x.status='CSOSHIP' and x.direct_type='IN' and x.order_nr=a.order_nr and x.rma_site=a.rma_site and rownum=1) track_nr_rtn,");
		buf.append("			e.oem_pn,");
		buf.append("			e.order_keyin_no");
		buf.append("		from");
		buf.append("			ers_order a,");
		buf.append("			ers_order_extra b,");
		buf.append("			ers_order_hist c,");
		buf.append("			ers_edi_motion_header d,");
		buf.append("			ers_item e");
		buf.append("		where");
		buf.append("			a.dt_id is null");
		buf.append("			and a.order_no=b.order_no");
		buf.append("			and a.rma_site=b.rma_site");
		buf.append("			and a.order_no=c.order_no");
		buf.append("			and a.rma_site=c.rma_site");
		buf.append("			and a.order_no=d.order_no");
		buf.append("			and a.rma_site=d.rma_site");
		buf.append("			and a.order_no=e.order_no");
		buf.append("			and a.rma_site=e.rma_site");
		buf.append("			and a.rma_site IN (62, 64)");
		buf.append("			and a.order_nr='" + orderNr + "'");
		buf.append("		)x group by x.order_no,x.rma_site,x.case_number,x.case_handle,x.case_entity_handle,x.receive_date,x.ship_date,x.closed_date,x.track_nr,x.track_nr_rtn,x.oem_pn,x.order_keyin_no");
		buf.append("	order by x.order_keyin_no");
		return buf;
	}

	@Override
	public Smbxml processMessage(Smbxml smb, String key, ResultSet rs) throws Exception {
		Request request = new Request();
		request.setMissingFieldOverwrite("false");
		AddOrUpdate addOrUpdate = new AddOrUpdate();
		Case case_ = new Case();
		CustomFieldValues customFieldValues = new CustomFieldValues();
		Map<String, Map> map = new HashMap<String, Map>();
		while (rs.next()) {
			Map data = new HashMap();
			BigDecimal qty = rs.getBigDecimal("qty");
			String partNr = rs.getString("part_nr");
			if (map.containsKey(partNr)) {
				Map subMap = (Map) map.get(partNr);
				qty = qty.add(new BigDecimal(subMap.get("QTY").toString()));
			}
			data.put("part_nr", rs.getString("part_nr"));
			data.put("QTY", qty.toString());
			data.put("case_handle", rs.getString("case_handle"));
			data.put("case_number", rs.getString("case_number"));
			data.put("ship_date", rs.getString("ship_date"));
			data.put("track_nr", rs.getString("track_nr"));
			data.put("track_nr_rtn", rs.getString("track_nr_rtn"));
			map.put(partNr, data);
		}

		int i = 0;
		for (Map data : map.values()) {
			i++;
			case_.setHandle((String) data.get("case_handle"));
			case_.setCaseNumber((String) data.get("case_number"));
			CustomFieldValue customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Order Status");
			customFieldValue.setvalue("Shipped");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Product Shipped");
			customFieldValue.setvalue((String) data.get("ship_date"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Shipment Tracking #");
			customFieldValue.setvalue((String) data.get("track_nr"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Shipment Tracking - CMP Return");
			customFieldValue.setvalue((String) data.get("track_nr_rtn"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);
			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Part " + (i) + " - Part Consumed");
			customFieldValue.setvalue((String) data.get("part_nr"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Part " + (i) + " - Consumed QTY");
			customFieldValue.setvalue((String) data.get("QTY"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);
		}
		case_.setCustomFieldValues(customFieldValues);
		addOrUpdate
				.getAccountOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrTopicOrSolutionOrCaseOrEventOrTaskOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategory()
				.add(case_);
		request.getAddOrAddOrUpdateOrQueryOrGet().add(addOrUpdate);
		smb.getHeaderOrRequestOrResponse().add(request);
		return smb;
	}
}
